package com.gxa.dao.impl;

import com.gxa.dao.BuildDao;
import com.gxa.entity.Build;
import com.gxa.entity.Community;
import com.gxa.utils.DBConnection;
import jdk.nashorn.internal.objects.NativeUint8Array;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 栋数 数据访问层（持久层） 实现类
 */
public class BuildDaoImpl implements BuildDao {

    @Override
    public List<Build> selectAllBuildByComm(Build build) throws SQLException {
        // 1、获取连接
        Connection connection = DBConnection.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Build> buildList = new ArrayList<>();
        try {
            // 2、准备SQL
            String sql = "SELECT DISTINCT c.cid,c.cname,c.address,c.area,c.creator,c.managers,c.create_time,c.remark,b.bid,b.bname,b.unit_num,b.unit\n" +
                    "FROM community c,build b\n" +
                    "WHERE c.cid = b.community_id AND c.cid = ?\n" +
                    "GROUP BY b.bname";
            // 3、获取预编译对象，预编译SQL
            ps = connection.prepareStatement(sql);
            // 4、传入参数
            ps.setInt(1,build.getBid());
            // 5、执行SQL语句
            rs = ps.executeQuery();
            // 6、处理查询结果集
            while (rs.next()){
                int bid = rs.getInt("bid");
                String bname = rs.getString("bname");
                int unit = rs.getInt("unit");

                Build build1 = new Build(bid,bname,unit);
                buildList.add(build1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 7、释放资源
            DBConnection.close(rs,ps,connection);
        }

        return buildList;

    }

    @Override
    public List<Build> selectAllUnitByBuildAndComm(Build build) throws SQLException {
        // 1、获取连接
        Connection connection = DBConnection.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Build> buildList = new ArrayList<>();
        try {
            // 2、准备SQL
            String sql = "SELECT DISTINCT c.cid,c.cname,c.address,c.area,c.creator,c.managers,c.create_time,c.remark,b.bid,b.bname,b.unit_num,b.unit\n" +
                    "FROM community c,build b\n" +
                    "WHERE c.cid = b.community_id AND c.cid = ? AND b.bname = ?\n" +
                    "group by b.unit;";
            // 3、获取预编译对象，预编译SQL
            ps = connection.prepareStatement(sql);
            // 4、传入参数
            ps.setInt(1,build.getBid());
            ps.setString(2, build.getBname());
            // 5、执行SQL语句
            rs = ps.executeQuery();
            // 6、处理查询结果集
            while (rs.next()){
                int bid = rs.getInt("bid");
                String bname = rs.getString("bname");
                int unit = rs.getInt("unit");

                Build build1 = new Build(bid,bname,unit);
                buildList.add(build1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 7、释放资源
            DBConnection.close(rs,ps,connection);
        }

        return buildList;
    }

    @Override
    public List<Build> selectAllBuild() throws SQLException {
        // 1、获取连接
        Connection connection = DBConnection.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Build> buildList = new ArrayList<>();
        try {
            // 2、准备SQL
            String sql = "SELECT bid,bname FROM build\n" +
                    "GROUP BY bname";
            // 3、获取预编译对象，预编译SQL
            ps = connection.prepareStatement(sql);
            // 4、传入参数

            // 5、执行SQL语句
            rs = ps.executeQuery();
            // 6、处理查询结果集
            while (rs.next()){
                int bid = rs.getInt("bid");
                String bname = rs.getString("bname");
                int unit = rs.getInt("unit");

                Build build1 = new Build(bid,bname,unit);
                buildList.add(build1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 7、释放资源
            DBConnection.close(rs,ps,connection);
        }

        return buildList;

    }

    @Override
    public List<Build> selectAllBuildByPage(int pageToInt, int limitToInt) throws SQLException {
        // 1、获取连接
        Connection connection = DBConnection.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Build> buildList = new ArrayList<>();
        try {
            // 2、准备SQL
            String sql = "SELECT b.BID, b.BNAME, b.UNIT_NUM, b.UNIT, b.COMMUNITY_ID,  c.cid, c.cname, c.address, c.area, c.creator, c.managers, c.create_time, c.remark\n" +
                    "FROM build b\n" +
                    "    JOIN community c on c.cid = b.community_id\n" +
                    "ORDER BY b.bid ASC\n" +
                    "LIMIT ?,?";
            // 3、获取预编译对象，预编译SQL
            ps = connection.prepareStatement(sql);
            // 4、传入参数
            ps.setInt(1,(pageToInt - 1) * limitToInt);
            ps.setInt(2,limitToInt);
            // 5、执行SQL语句
            rs = ps.executeQuery();
            // 6、处理查询结果集
            while (rs.next()){
                // 栋数
                int bid = rs.getInt("bid");
                String bname = rs.getString("bname");
                int unit_num = rs.getInt("unit_num");
                int unit = rs.getInt("unit");
                int cid = rs.getInt("community_id");
                // 小区
                String cname = rs.getString("cname");
                String address = rs.getString("address");
                double area = rs.getDouble("area");
                String creator = rs.getString("creator");
                String managers = rs.getString("managers");
                // java.sql.Date 通过时间戳转换为 java.util.Date
                Date create_time = rs.getDate("create_time");
                long time = create_time.getTime();
                Date createTime = new Date(time);

                String remark = rs.getString("remark");
                // 封装小区对象
                Community community = new Community(cid, cname,address,area,creator,managers,createTime,remark);

                Build build1 = new Build(bid,bname,unit,unit_num,community);
                buildList.add(build1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 7、释放资源
            DBConnection.close(rs,ps,connection);
        }

        return buildList;

    }

    @Override
    public int selectCounts() throws SQLException {
        // 1、获取连接
        Connection connection = DBConnection.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        int total = 0;
        try {
            // 2、准备SQL
            String sql = "SELECT COUNT(1) AS total FROM build";
            // 3、获取预编译对象，预编译SQL
            ps = connection.prepareStatement(sql);
            // 4、传入参数

            // 5、执行SQL语句
            rs = ps.executeQuery();
            // 6、处理查询结果集
            while (rs.next()){
                total = rs.getInt("total");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 7、释放资源
            DBConnection.close(rs,ps,connection);
        }

        return total;


    }

    @Override
    public int insertBuild(Build build) {
        Connection connection = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            // 1、注册驱动
            // 2、获取连接
            connection = DBConnection.getConnection();
            System.out.println("Dao层的conn===>"+connection);

            String sql = "INSERT INTO build (bname, unit_num, unit, community_id) VALUES (?,?,?,?)";
            // 3、获取数据库预编译对象
            ps = connection.prepareStatement(sql);
            //传入参数
            ps.setString(1, build.getBname());
            ps.setInt(2, build.getUnitNum());
            ps.setInt(3, build.getUnit());
            ps.setInt(4, build.getCommunity().getCid());
            // 4、执行SQL语句
            count = ps.executeUpdate();
            // 5、有查询操作需要处理查询结果集
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 6、释放资源
            DBConnection.close(null,ps,null);
        }

        return count;
    }

    @Override
    public int editBuild(Build build) {
        Connection connection = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            // 1、注册驱动
            // 2、获取连接
            connection = DBConnection.getConnection();
            System.out.println("Dao层的conn===>"+connection);

            String sql = "UPDATE build SET bname = ?, unit_num = ?,unit = ?, community_id = ? WHERE bid = ?";
            // 3、获取数据库预编译对象
            ps = connection.prepareStatement(sql);
            //传入参数
            ps.setString(1, build.getBname());
            ps.setInt(2, build.getUnitNum());
            ps.setInt(3, build.getUnit());
            ps.setInt(4, build.getCommunity().getCid());
            ps.setInt(5,build.getBid());
            // 4、执行SQL语句
            count = ps.executeUpdate();
            // 5、有查询操作需要处理查询结果集
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 6、释放资源
            DBConnection.close(null,ps,null);
        }

        return count;
    }

    @Override
    public int delBuildByBid(Integer bid) {
        Connection connection = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            // 1、注册驱动
            // 2、获取连接
            connection = DBConnection.getConnection();
            System.out.println("Dao层的conn===>"+connection);

            String sql = "DELETE FROM build WHERE bid = ?";
            // 3、获取数据库预编译对象
            ps = connection.prepareStatement(sql);
            //传入参数
            ps.setInt(1, bid);

            // 4、执行SQL语句
            count = ps.executeUpdate();
            // 5、有查询操作需要处理查询结果集
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 6、释放资源
            DBConnection.close(null,ps,connection);
        }

        return count;
    }
}
